<?php
!defined('P_W') && exit('Forbidden');
/**
 * 
 * 二手房DB类
 * @author hu.liaoh
 *
 */
class PW_House_SecondDB extends BaseDB{
	var $_primaryKey  = '';
	var $_saleInfo = '';
	var $_msyqlSort         = "DESC";
	var $_allowIndexFields  = array();
	var $_sortTypes         = array();
	var $_sortFields        = array();
	var $_sortFieldTables   = array();
	
	function PW_House_SecondDB(){
		parent::BaseDB();
		$this->_sortTypes = array('DESC', 'ASC');	//允许排序的方式	
		$this->_expandFields = array('equal'=>array(),'like'=>array());//扩展字段
	}
	
	/**
	 * 
	 * 获取二手房信息列表
	 * @param $arrayIndex 索引数组
	 * @param $sort 排序方式
	 * @param $sortField 排序字段
	 * @param $keywords 关键字数组
	 * @param $offset 分页开始
	 * @param $limit 分页限制
	 * @param $expand 扩展参数
	 */
	function getList($arrayIndex, $sort, $sortField, $keywords, $page, $limit, $expand=array()){
		$page = intval($page);
		$offset = ($page -1) * intval($limit);
		$arrayIndex = $this->_filterIndexField($arrayIndex);
		$_sqlLimit = $limit ? $this->_Limit($offset, $limit) : '';
		$sort = $this->_filterSortWay($sort);
		$_sortField = $this->_filterSortField($sortField);
		$_tableName = $this->_getIndexTable($_sortField);
		$_expand = $this->_filterExpandFields($expand);
		$keywords = $this->_filterKeywords($keywords);
		isset($_expand['state']) && $_expand['state'] && $_tableName = $this->_overdueTable;
		return $this->_getListByConditions($_tableName, $arrayIndex, $sort, $_sortField, $keywords, $_sqlLimit, $_expand);
	}
	
	function _getListByConditions($_tableName, $arrayIndex, $sort, $_sortField, $keywords, $_sqlLimit, $_expand){
		if(S::isArray($arrayIndex) && (S::isArray($keywords) || S::isArray($_expand))) return $this->_getListByExpand($_tableName, $arrayIndex, $sort, $_sortField, $keywords, $_sqlLimit, $_expand);
		if(S::isArray($arrayIndex)) return $this->_getListByIndex($_tableName, $arrayIndex, $sort, $_sortField, $_sqlLimit);
		if(S::isArray($keywords) || S::isArray($_expand)) return $this->_getListByKeyexpand($_tableName, $sort, $_sortField, $keywords, $_sqlLimit, $_expand);
		return $this->_getListByOrder($_tableName, $sort, $_sortField, $_sqlLimit);
	}
	
	function _getListByExpand($_tableName, $arrayIndex, $sort, $_sortField, $keywords, $_sqlLimit, $_expand){
		list($total, $houseids) = $this->_getHouseidsByIndex($_tableName, $arrayIndex, $sort, $_sortField, '');
		if(!$total) return array(0, array());	
		!isset($_expand['state']) && $_expand['state'] = 0;
		$_expandSql = $this->_getExpentSql($_expand);
		$_sqlWhere = $this->_getSqlWhere($keywords);
		$total = $this->_getCount('SELECT COUNT(*) as total FROM ' . $this->_saleInfo . '  WHERE houseid IN (' . pwImplode(array_keys($houseids)) .') ' . $_sqlWhere . $_expandSql);
		if(!$total) return array(0, array());	
		$_sql = 'SELECT * FROM ' . $this->_saleInfo . ' WHERE houseid IN (' . pwImplode(array_keys($houseids)) .') ' . $_sqlWhere . $_expandSql . $_sqlLimit;
		$result = $this->_getResult($_sql, $this->_primaryKey);
		return array($total, $result);
	}
	
	function _getListByKeyexpand($_tableName, $sort, $_sortField, $keywords, $_sqlLimit, $_expand){
		list($total, $houseids) = $this->_getHouseidsByExpandAndOrder($_tableName, $sort, $_sortField, $keywords, $_sqlLimit, $_expand);
		if(!$total) return array(0, array());
		$result = $this->_getSalesByHouseids(array_keys($houseids));
		return array($total, $result);
	}
	
	function _getHouseidsByExpandAndOrder($_tableName, $sort, $_sortField, $keywords, $_sqlLimit, $_expand){
		!isset($_expand['state']) && $_expand['state'] = 0;
		$_expandSql = $this->_getExpentSql($_expand);
		$_sqlWhere = $this->_getSqlWhere($keywords);
		$total = $this->_getCount('SELECT COUNT(*) as total FROM ' . $this->_saleInfo . ' WHERE 1 ' . $_expandSql . $_sqlWhere);
		if(!$total) return array(0, array());
		$_sql = 'SELECT info.houseid FROM '. $_tableName .' as pindex JOIN (SELECT houseid FROM ' . $this->_saleInfo . ' WHERE 1 ' . $_expandSql . $_sqlWhere .' ) AS info ON info.houseid = pindex.houseid ORDER BY pindex.' . $_sortField .' '. $sort . $_sqlLimit;
		$result = $this->_getResult($_sql, $this->_primaryKey);
		return array($total, $result);
	}
	
	function _getHouseidsByIndex($_tableName, $arrayIndex, $sort, $_sortField, $_sqlLimit){
		$_sqlWhere = "";
		foreach ($arrayIndex as $key=>$value){
			$_sqlWhere.=" AND "	. $key . '=' . $this->_addSlashes($value);
		}
		$total = $this->_getCount('SELECT COUNT(*) as total FROM ' . $_tableName . ' WHERE 1 ' . $_sqlWhere);
		if(!$total) return array(0, array());
		$_sql = 'SELECT houseid FROM ' . $_tableName . ' AS pindex WHERE 1 ' . $_sqlWhere . ' ORDER BY ' . $_sortField . ' ' . $sort . $_sqlLimit;
		$result = $this->_getResult($_sql, $this->_primaryKey);
		return array($total, $result);
	}
	
	function _getHouseidsByOrder($_tableName, $sort, $_sortField, $_sqlLimit){
		$total = $this->_getCount('SELECT COUNT(*) as total FROM ' . $_tableName);
		if(!$total) return array(0,array());
		$_sql = 'SELECT houseid FROM ' . $_tableName . ' ORDER BY ' . $_sortField . ' ' . $sort . ' ' . $_sqlLimit;
		$result = $this->_getResult($_sql, $this->_primaryKey);
		return array($total, $result);
	}
	
	/**
	 * 
	 * 普通查询
	 * @param $_tableName
	 * @param $sort
	 * @param $_sortField
	 * @param $_sqlLimit
	 */
	function _getListByOrder($_tableName, $sort, $_sortField, $_sqlLimit){
		list($total, $houseids) = $this->_getHouseidsByOrder($_tableName, $sort, $_sortField, $_sqlLimit);
		if(!$total) return array(0, array());
		$result = $this->_getSalesByHouseids(array_keys($houseids));
		return array($total, $result);
	}
	
	
	
	/**
	 * 
	 * 根据索引条件获取列表
	 * @param $_tableName 表名
	 * @param $arrayIndex 索引字段数组
	 * @param $sort 排序方式
	 * @param $_sortField 排序字段
	 * @param $_sqlLimit 查询限制
	 */
	function _getListByIndex($_tableName, $arrayIndex, $sort, $_sortField, $_sqlLimit){
		list($total, $houseids) = $this->_getHouseidsByIndex($_tableName, $arrayIndex, $sort, $_sortField, $_sqlLimit);	//获取索引集合
		if(!$total) return array(0, array());
		$result = $this->_getSalesByHouseids(array_keys($houseids));//根据houseid集合获取信息集合
		return array($total, $result);
	}
	
	/**
	 * 
	 * 根据查询关键字获取列表
	 * @param $sort 排序方式 
	 * @param $_sortField 排序字段 
	 * @param $keywords 关键字数组
	 * @param $_sqlLimit 查询限制
	 */
	function _getSalesByKeywords($sort, $_sortField, $keywords, $_sqlLimit){
		$_sqlWhere = $this->_getSqlWhere($keywords);
		$total = $this->_getCount('SELECT COUNT(*) as total FROM ' . $this->_saleInfo . ' WHERE state = 0 ' . $_sqlWhere);
		if(!$total) return array(0,array());
		$_sql ='SELECT * FROM ' . $this->_saleInfo . ' WHERE state = 0 AND 1 ' . $_sqlWhere . ' ORDER BY ' . $_sortField . ' ' . $sort . ' ' . $_sqlLimit;
		$result = $this->_getResult($_sql, $this->_primaryKey);
		return array($total, $result);
	}
	
	/**
	 * 
	 *  根据houseid获取索引集合
	 * @param $_tableName 表名
	 * @param $houseids houseid数组
	 */
	function _getIndexsByHouseids($_tableName ,$houseids){
		if(empty($houseids)) return array();
		$_sql = 'SELECT * FROM ' . $_tableName . ' s WHERE houseid IN (' . pwImplode($houseids) . ')';
		return $this->_getResult($_sql, $this->_primaryKey);
	}
	
	/**
	 * 
	 * 根据houseid获取信息集合
	 * @param $houseids
	 */
	function _getSalesByHouseids($houseids){
		if(empty($houseids)) return array();
		$_sql = 'SELECT * FROM ' .$this->_saleInfo. ' WHERE houseid IN (' . pwImplode($houseids) . ')';
		$result = $this->_getResult($_sql,$this->_primaryKey);
		$tmp = array();
		foreach ($houseids as $key){
			$tmp[$key] = $result[$key];
		}
		return $tmp;
	}
	
	/**
	 * 
	 * 根据索引条件查询所有id
	 * @param $_tableName 表名
	 * @param $arrayIndex 索引数组
	 * @param $sort 排序方式 
	 * @param $_sortField 排序字段 
	 * @param $_sqlLimit 查询限制
	 */
	function _getIdsByIndex($_tableName, $arrayIndex, $sort, $_sortField, $_sqlLimit){
		$_sqlWhere = "";
		foreach ($arrayIndex as $key=>$value){
			$_sqlWhere.=" AND "	. $key . '=' . $this->_addSlashes($value);
		}
		$total = $this->_getCount('SELECT COUNT(*) as total FROM ' . $_tableName . ' WHERE 1 ' . $_sqlWhere);
		if(!$total) return array(0, array());
		$_sql = 'SELECT * FROM ' . $_tableName . ' s WHERE 1 ' . $_sqlWhere . ' ORDER BY ' . $_sortField . ' ' . $sort . $_sqlLimit;
		$result = $this->_getResult($_sql, $this->_primaryKey);
		return array($total, $result);
	}
	
	function _getSqlWhere($keywords){
		$_sqlWhere = "";
		if(is_array($keywords) && !empty($keywords)){
			foreach ($keywords as $keyword){
				$_sqlWhere.= $_sqlWhere ? " OR " : " AND (";
				$_sqlWhere.= $this->_saleInfo.".gate LIKE " . $this->_addSlashes('%'.$keyword.'%');
			}
			$_sqlWhere.= ")";
		}
		return $_sqlWhere;
	}
	/**
	 * 
	 * 获取扩展SQL字符串
	 * @param $_expand 扩展数组
	 */
	function _getExpentSql($_expand){
		if(empty($_expand)) return '';
		 foreach ($_expand as $key=>$value){
		 	if(in_array($key, $this->_expandFields['equal'])) $_expand_sql.= " AND ".$this->_saleInfo.".".$key."=".$this->_addSlashes($value);
		 	if(in_array($key, $this->_expandFields['like'])) $_expand_sql.=  " AND ".$this->_saleInfo.".".$key." LIKE " . $this->_addSlashes('%'.$value.'%');
		 }
		 return $_expand_sql;
	}
	
	function _getIndexTable($_sortField){
		return $this->_sortFieldTables[$_sortField];
	}
	
	/**
	 * 
	 * 过滤输入数组为索引字段
	 * @param $field 索引字段数组
	 */
	function _filterIndexField($field) {
		$tmp = array();
		if(empty($field)) return array();
		foreach ($this->_allowIndexFields as $key => $value) {
			!empty($field[$value]) && $tmp[$value] = $field[$value];
		}
		return $tmp;
	}
	
	/**
	 * 
	 * 过滤查询关键字
	 * @param $keywords 关键字数组
	 */
	function _filterKeywords($keywords){
		!is_array($keywords) && $keywords = array($keywords);
		$tmp = array();
		foreach ($keywords as $key=>$value){
			if(!empty($value) && $value !== "")	$tmp[] = $value;
		}
		return $tmp;
	}
	
	/**
	 * 
	 * 检查排序类型是否存在
	 * @param $_sort 排序方式
	 */
	function _filterSortWay($sort){
		return in_array($sort, $this->_sortTypes) ? $sort : $this->_msyqlSort;
	}
	
	/**
	 * 
	 * 过滤扩展字段
	 * @param $expands
	 */
	function _filterExpandFields($expands){
		if(empty($expands)) return array();
		foreach ($expands as $key=>$value){
			if(!in_array($key, $this->_expandFields['equal']) && !in_array($key, $this->_expandFields['like']))	unset($expands[$key]);
		}
		return $expands;
	}
	
	/**
	 * 
	 * 获取total
	 * @param $sql SQL字符串
	 */
	function _getCount($sql){
		$result = $this->_db->get_one($sql);
		return ($result) ? $result['total'] : 0;
	}
	
	/**
	 * 
	 * 获取查询结果集
	 * @param $sql  SQL字符串
	 * @param $indexKey 作为数组键的字段
	 */
	function _getResult($sql, $indexKey=''){
		$query = $this->_db->query($sql);
		return $this->_getAllResultFromQuery($query, $indexKey);
	}
	
	/**
	 * 
	 * 过滤排序 字段 
	 * @param $sortField
	 */
	function _filterSortField($sortField){
		return in_array($sortField, array_keys($this->_sortFieldTables)) ? $sortField : 'modifytime';
	}
}